package com.vasthu.core.dao;

import com.vasthu.api.dao.ItemDAO;
import com.vasthu.api.domain.Item;
import java.util.*;
import java.sql.*;

public class ItemDAOImpl implements ItemDAO {

	public boolean insertItem(Connection con, Item item) {
		if (item == null)
			return false;
			
		PreparedStatement pst = null;
		
		try {
			pst = con.prepareStatement("INSERT INTO Item(item_code, item_name, item_desc, item_brand, item_cat_id, status) VALUES(?, ?, ?, ?, ?, 1)");
			
            pst.setString(1, item.getItemCode());
            pst.setString(2, item.getItemName());
            pst.setString(3, item.getItemDesc());
            pst.setString(4, item.getItemBrand());
            pst.setLong(5, item.getItemCategoryId());

            pst.executeUpdate();
			
		} catch (Exception ex) {
			System.out.println("ERROR : ItemDAOImpl->insertItem()");
		}
		
		return true;
	}

	public Item getItemById(Connection con, long itemId) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		Item item = new Item();
		
		try {
			pst = con.prepareStatement("SELECT * FROM Item WHERE item_id = ? AND status = 1");
			pst.setLong(1, itemId);
			rs = pst.executeQuery();
			
			while (rs.next()) {
				item.setItemId( rs.getInt("item_id") );
				item.setItemCode( rs.getString("item_code"));
				item.setItemName( rs.getString("item_name") );
				item.setItemDesc( rs.getString("item_desc") );
				item.setItemBrand( rs.getString("item_brand") );
				item.setItemCategoryId( rs.getLong("item_cat_id") );
				break;
            }
			
		} catch (Exception ex) {
			System.out.println("ERROR : ItemDAOImpl->deleteItemById()");
		}
		
		return item;
	}
	
	public Item getItemByCode(Connection con, String itemCode) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		Item item = new Item();
		
		try {
			pst = con.prepareStatement("SELECT * FROM Item WHERE item_code = ? AND status = 1");
			pst.setString(1, itemCode);
			rs = pst.executeQuery();
			
			while (rs.next()) {
				item.setItemId( rs.getInt("item_id") );
				item.setItemCode( rs.getString("item_code"));
				item.setItemName( rs.getString("item_name") );
				item.setItemDesc( rs.getString("item_desc") );
				item.setItemBrand( rs.getString("item_brand") );
				item.setItemCategoryId( rs.getLong("item_cat_id") );
				break;
            }
			
		} catch (Exception ex) {
			System.out.println("ERROR : ItemDAOImpl->deleteItemByName()");
		}
		
		return item;
	}
	
	public boolean deleteItemById(Connection con, long itemId) {
		PreparedStatement pst = null;

		try {
			pst = con.prepareStatement("DELETE FROM Item WHERE item_id = ?");
			pst.setLong(1, itemId);
			pst.executeUpdate();
		} catch (Exception ex) {
			System.out.println("ERROR : ItemDAOImpl->deleteItemById()");
		}
		
		return true;
	}
    
    public List<Item> getAllItems(Connection con) {
		List<Item> itemList = new ArrayList<Item>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		try {
            pst = con.prepareStatement("SELECT * FROM Item WHERE status = 1");
            rs = pst.executeQuery();

			while (rs.next()) {
				Item item = new Item();
				
				item.setItemId( rs.getInt("item_id") );
				item.setItemCode( rs.getString("item_code"));
				item.setItemName( rs.getString("item_name") );
				item.setItemDesc( rs.getString("item_desc") );
				item.setItemBrand( rs.getString("item_brand") );
				item.setItemCategoryId( rs.getLong("item_cat_id") );
				
				itemList.add(item);
            }

        } catch (SQLException ex) {
            System.out.println("ERROR : ItemDAOImpl->getAllItems()");
        }
		
		return itemList;
	}
}